﻿using DBUtil.Attributes;
using DotNetCommon.Extensions;
using NUnit.Framework;
using Shouldly;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text.Json.Nodes;

namespace Test.MySql.JsonTests.AnyAllTests.simple
{
    [TestFixture]
    internal class JsonArrayTestsSimple : TestBase
    {
        #region model
        [Table("test")]
        public class Person
        {
            [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
            [Column("id")]
            public int Id { get; set; }

            [JsonStore(Bucket = "Names")]
            public JsonArray Names { get; set; }

            [JsonStore(Bucket = "ext", Key = "Scores")]
            public JsonArray Scores { get; set; }
        }
        #endregion

        [SetUp]
        public void SetUp()
        {
            DropTableIfExist("test");
            db.ExecuteSql("create table test(id int primary key auto_increment,ext json,Names json)");
            db.ExecuteSql("""
                insert into test(ext,Names) values
                    ('{"Scores":[86,95.5,76.25,60]}','["jack","tom","lisa"]'),
                    ('{"Scores":[86,100,76.25,40]}','["jack","tom2","lisa2"]'),
                    ('{"Scores":[86,95.5,76.25,60]}','[]');
                """);
        }

        [Test]
        public void TestAny1()
        {
            var select = db.Select<Person>().Where(i => i.Names.Any());
            var sql = select.ToSql();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.Names,'$') `Names`,json_value(t.ext,'$."Scores"') `Scores`
            //    from test t
            //    where json_length(json_value(t.Names,'$'))>0;
            //    """);
            var json = select.ToList().ToJson();
            json.ShouldBe("""[{"Id":1,"Names":["jack","tom","lisa"],"Scores":[86,95.5,76.25,60]},{"Id":2,"Names":["jack","tom2","lisa2"],"Scores":[86,100,76.25,40]}]""");
        }

        [Test]
        public void TestAny2()
        {
            var select = db.Select<Person>().Where(i => i.Scores.Any(i => i.GetValue<double>() < 60));
            var sql = select.ToSql();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.Names,'$') `Names`,json_value(t.ext,'$."Scores"') `Scores`
            //    from test t
            //    where 0<(select 1 from json_table(json_value(t.ext,'$."Scores"'),'$[*]' columns(`i` json path '$')) t where (json_value(t.`i`,'$' returning double)) < 60 limit 1);
            //    """);
            var json = select.ToList().ToJson();
            json.ShouldBe("""[{"Id":2,"Names":["jack","tom2","lisa2"],"Scores":[86,100,76.25,40]}]""");
        }
        [Test]
        public void TestAny3()
        {
            var select = db.Select<Person>().Where(i => i.Names.Any(i => i.GetValue<string>().StartsWith("jack")));
            var sql = select.ToSql();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.Names,'$') `Names`,json_value(t.ext,'$."Scores"') `Scores`
            //    from test t
            //    where 0<(select 1 from json_table(json_value(t.Names,'$'),'$[*]' columns(`i` json path '$')) t where (json_value(t.`i`,'$' returning char)) like 'jack%' limit 1);
            //    """);
            var json = select.ToList().ToJson();
            json.ShouldBe("""[{"Id":1,"Names":["jack","tom","lisa"],"Scores":[86,95.5,76.25,60]},{"Id":2,"Names":["jack","tom2","lisa2"],"Scores":[86,100,76.25,40]}]""");
        }

        [Test]
        public void TestAll1()
        {
            db.ExecuteSql("""insert into test(Names) values('[null]');""");

            var select = db.Select<Person>().Where(i => i.Names.All(i => i.ToString().IsNotNullOrEmptyOrWhiteSpace()));
            var sql = select.ToSql();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.Names,'$') `Names`,json_value(t.ext,'$."Scores"') `Scores`
            //    from test t
            //    where json_length(json_value(t.Names,'$'))=(select count(1) from json_table(json_value(t.Names,'$'),'$[*]' columns(`i` json path '$')) t where not (convert(t.`i`,char) is null or length(convert(t.`i`,char)) = 0));
            //    """);
            var json = select.ToList().ToJson();
            //最后一个 Names:[] 也能够被遍历出来, 因为它是集合为空, 并不是 集合内的元素为null, 可以尝试:
            //new List<string>().All(i => i.IsNotNullOrEmptyOrWhiteSpace()) // 这个得到的也是true
            json.ShouldBe("""[{"Id":1,"Names":["jack","tom","lisa"],"Scores":[86,95.5,76.25,60]},{"Id":2,"Names":["jack","tom2","lisa2"],"Scores":[86,100,76.25,40]},{"Id":3,"Names":[],"Scores":[86,95.5,76.25,60]}]""");
        }

        [Test]
        public void TestAll2()
        {
            var select = db.Select<Person>().Where(i => i.Scores.All(i => i.To<double>() > 50));
            var sql = select.ToSql();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.Names,'$') `Names`,json_value(t.ext,'$."Scores"') `Scores`
            //    from test t
            //    where json_length(json_value(t.ext,'$."Scores"'))=(select count(1) from json_table(json_value(t.ext,'$."Scores"'),'$[*]' columns(`i` json path '$')) t where convert(t.`i`,decimal(30,15)) > 50);
            //    """);
            var json = select.ToList().ToJson();
            json.ShouldBe("""[{"Id":1,"Names":["jack","tom","lisa"],"Scores":[86,95.5,76.25,60]},{"Id":3,"Names":[],"Scores":[86,95.5,76.25,60]}]""");
        }
    }
}
